package com.sq.partner.manager.dao.impl;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.sq.partner.manager.dao.IGatewayTradInsuPlanDao;
import com.sq.partner.manager.mybatis.model.DeptDto;
import com.sq.partner.manager.mybatis.model.TradAgentInfo;
import com.sq.partner.manager.mybatis.model.TradInsuPlan;
import com.sq.partner.manager.util.DBUtil;
import com.sq.partner.manager.util.LogUtil;
import com.sq.partner.manager.util.Page;
import com.sq.partner.manager.util.SqlUtil;
import com.sq.partner.manager.util.StrUtil;
@Repository
public class GatewayTradInsuPlanDaoImpl  extends BaseDaoImpl  implements IGatewayTradInsuPlanDao {
	private static Logger logger = LogUtil.getLog(GatewayTradInsuPlanDaoImpl.class);

	@Override
	public Page<TradInsuPlan> queryTradInsuPlanPage(Page<TradInsuPlan> page) throws SQLException {
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			sqlbuf.append("select tradInsuPlan.INSU_PLAN_NO as insuPlanNo,"
					+ "tradInsuPlan.INSU_PLAN_NAME as insuPlanName,"
					+ "tradInsuPlan.PARTNER_NO as partnerNo,"
					+ "tradProdInst.TRAD_PROD_CODE as tradProdCode,"
					+ "tradProdInst.TRAD_PROD_DESC as tradProdDesc,"
					+ "channelinfo.Channel_Name as partnerName "
					+ "from T_TRAD_INSU_PLAN tradInsuPlan "
					+ "left outer join T_TRAD_PROD_INST tradProdInst on tradInsuPlan.TRAD_PROD_CODE=tradProdInst.TRAD_PROD_CODE "
					+ "left outer join t_gateway_channel_info channelinfo on tradInsuPlan.PARTNER_NO = channelinfo.Channel_Code "
					+ "where 1=1 ");
			String sql;
			List<String> list=new ArrayList<String>();
			List<Boolean> islike=new ArrayList<Boolean>();
			TradInsuPlan tradInsuPlan= page.getType();
			if(StrUtil.isNotEmpty(tradInsuPlan.getPartnerNo())){
				sqlbuf.append(" AND tradInsuPlan.PARTNER_NO = ?");
				list.add(tradInsuPlan.getPartnerNo());
				islike.add(false);
			}
			if(StrUtil.isNotEmpty(tradInsuPlan.getInsuPlanNo())){
				sqlbuf.append(" AND tradInsuPlan.INSU_PLAN_NO like ?");
				list.add(tradInsuPlan.getInsuPlanNo());
				islike.add(true);
			}
			if(StrUtil.isNotEmpty(tradInsuPlan.getInsuPlanName())){
				sqlbuf.append(" AND tradInsuPlan.INSU_PLAN_NAME like ?");
				list.add(tradInsuPlan.getInsuPlanName());
				islike.add(true);
			}
			if(StrUtil.isNotEmpty(tradInsuPlan.getTradProdCode())){
				sqlbuf.append(" AND tradInsuPlan.TRAD_PROD_CODE like ?");
				list.add(tradInsuPlan.getTradProdCode());
				islike.add(true);
			}
			
			List<DeptDto> depts=page.getDepts();
			sqlbuf.append(" AND channelinfo.DEPT_CODE IN (");
			if(null!=depts){
				StringBuffer deptbuf=new StringBuffer();
				for(int i=0;i<depts.size();i++){
					deptbuf.append("?,");
					list.add(depts.get(i).getDeptCode());
					islike.add(false);
				}
				String deptstr=deptbuf.toString();
				deptstr=deptstr.substring(0, deptstr.length()-1);
				sqlbuf.append("select dept_code from t_core_dept t connect by prior dept_code = snr_dept start with dept_code in ("+deptstr+")");
			}else{
				sqlbuf.append("''");
			}
			sqlbuf.append(" )");
			
			
			sqlbuf.append(" order by NVL(tradInsuPlan.oper_time,TO_DATE('1990/01/01', 'yyyy/mm/dd')) desc,tradInsuPlan.INSU_PLAN_NO desc ");
			sql=sqlbuf.toString();
			logger.debug("执行SQL：" + sql);


			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt.setObject(i+1, list.get(i));
				}
				
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);
			page.setTotal(total);
			//查出分页
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt1.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt1.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		return page;
	}

	@Override
	public Page<TradInsuPlan> queryAllTradInsuPlans(Page<TradInsuPlan> page) throws SQLException {
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			sqlbuf.append("select t.insu_plan_no as insuPlanNo,t.insu_plan_name as insuPlanName from T_TRAD_INSU_PLAN t where 1=1");
			String sql;
			List<String> list=new ArrayList<String>();
			List<Boolean> islike=new ArrayList<Boolean>();
			TradInsuPlan tradInsuPlan= page.getType();
			if(StrUtil.isNotEmpty(tradInsuPlan.getInsuPlanNo())){
				sqlbuf.append(" AND t.INSU_PLAN_NO like ?");
				list.add(tradInsuPlan.getInsuPlanNo());
				islike.add(true);
			}
			if(StrUtil.isNotEmpty(tradInsuPlan.getInsuPlanName())){
				sqlbuf.append(" AND t.INSU_PLAN_NAME like ?");
				list.add(tradInsuPlan.getInsuPlanName());
				islike.add(true);
			}
			
			sqlbuf.append(" order by t.INSU_PLAN_NO desc ");
			sql=sqlbuf.toString();
			logger.debug("执行SQL：" + sql);


			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt.setObject(i+1, list.get(i));
				}
				
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);
			page.setTotal(total);
			//查出分页
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt1.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt1.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(conn);
		}
		
		return page;
	}

	@Override
	public List<TradInsuPlan> queryNewBSNS(Map<String, String> map)
			throws Exception {
		return this.queryList("queryTradInsuPlanByMap", map);
	}

	@Override
	public boolean addTradInsuPlan(TradInsuPlan insuplan) throws Exception {
		return this.add("insertTradInsuPlan", insuplan);
	}

	@Override
	public boolean delTradInsuPlan(TradInsuPlan insuplan) throws Exception {
		return this.delete("delTradInsuPlan", insuplan);
	}

	@Override
	public TradInsuPlan queryTradInsuPlanInfo(TradInsuPlan insuplan)
			throws Exception {
		return (TradInsuPlan)this.query("queryTradInsuPlanById", insuplan);
	}

	@SuppressWarnings("unchecked")
	@Override
	public Map<String, Object> queryMapTradInsuPlanInfo(TradInsuPlan insuplan)
			throws Exception {
		
		return (Map<String, Object>)this.query("queryMapTradInsuPlanById", insuplan);
	}
	
	@Override
	public boolean updateTradInsuPlanInfo(TradInsuPlan insuplan)
			throws Exception {
		return this.update("updateTradInsuPlan", insuplan);
	}

	@Override
	public TradInsuPlan getMaxInsuPlanNo(TradInsuPlan insuplan)
			throws Exception {
		Object obj = this.query("queryMaxTradInsuPlanNo", insuplan);
		if(null!=obj){
			return (TradInsuPlan)obj;
		}
		return null;
	}

	@Override
	public List<TradInsuPlan> queryTradInsuPlanByPlanCode(String PlanCode)
			throws Exception {
		// TODO Auto-generated method stub
		return this.queryList("queryTradInsuPlanByPlanCode", PlanCode);
	}

	@Override
	public List<TradInsuPlan> queryTradInsuPlanByProdCode(
			String prodCode) throws SQLException {
		// TODO Auto-generated method stub
		return this.queryList("queryTradInsuPlanByProdCode",prodCode);
	}

	@Override
	public boolean isHasExistPlan(String partnerNo) throws Exception {
		// TODO Auto-generated method stub
		Integer result = (Integer)this.query("queryTradInsuPlanByPartnerNo", partnerNo);
		if(null!=result &&  Integer.valueOf(result)>0)
		{
			return true;
		}
		return false;
	}

	@Override
	public Page queryPageForTradAgentInfo(Page<TradAgentInfo> page,Map<String,Object> map)
			throws Exception {
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			sqlbuf.append(" SELECT T.ID AS ID,T.USER_CODE AS USERCODE,U.USER_NAME AS USERNAME,T.DPT_CDE AS DPTCDE, ")
			.append(" T.CHANNLE_CODE CHANNLECODE,D1.CODE_NAME AS CHANNLECODEDES,T.CHANNLE_TYPE AS CHANNLETYPE,D2.CODE_NAME AS CHANNLETYPEDES, ")
			.append(" T.NEW_BSNS_TYPE AS NEWBSNSTYPE,D3.CODE_NAME AS NEWBSNSTYPEDES,T.CATEGORY_CODE AS CATEGORYCODE,D4.CODE_NAME AS CATEGORYCODEDES, ")
			.append(" T.AGENT_NO AS AGENTNO,T.PROTOCOL_NO AS PROTOCOLNO, T.SERVICE_CODE AS SERVICECODE,T.EMP_NO AS EMPNO, ")
			.append(" T.CARD_APPLICANT AS CARDAPPLICANT, T.TEAM_CODE AS TEAMCODE ")
			.append(" FROM T_TRAD_AGENT_INFO T  ")
			.append(" LEFT JOIN T_FACADE_USER U ON T.USER_CODE = U.USER_CODE  ")
			.append(" LEFT JOIN t_gateway_dict d1 on d1.code_type ='belong_to_channel' and T.CHANNLE_CODE = d1.code ")
			.append(" LEFT JOIN t_gateway_dict d2 on d2.code_type ='channel_type' and T.Channle_Type = d2.code ")
			.append(" LEFT JOIN t_gateway_dict d3 on d3.code_type ='new_business_source' and T.New_Bsns_Type = d3.code ")
			.append(" LEFT JOIN t_gateway_dict d4 on d4.code_type ='old_business_source' and T.Category_Code = d4.code ")
			.append(" WHERE 1=1 ");
			
				
			String sql;
			List<String> list=new ArrayList<String>();
			List<Boolean> islike=new ArrayList<Boolean>();
			TradAgentInfo tradAgentInfo= page.getType();
			String channelCode =StrUtil.getVal(map.get("channelCode"));
			if(StrUtil.isNotEmpty(channelCode)){
				sqlbuf.append(" AND T.CHANNEL_CODE = ?");
				list.add(channelCode);
				islike.add(false);
			}
			if(StrUtil.isNotEmpty(tradAgentInfo.getAgentType())){
				sqlbuf.append(" AND T.AGENT_TYPE= ?");
				list.add(tradAgentInfo.getAgentType());
				islike.add(false);
			}
			String dept_code =StrUtil.getVal(map.get("dept_code"));
			if(StrUtil.isNotEmpty(dept_code)){
				sqlbuf.append(" and t.dpt_cde in (select dept_code from t_core_dept t connect by prior dept_code = snr_dept start with dept_code in ( ? ))");
				list.add(dept_code);
				islike.add(false);
			}
			sql=sqlbuf.toString();
			String confStatus = StrUtil.getVal(map.get("confStatus"));
			if("ALL".equalsIgnoreCase(confStatus)){
				sql = "SELECT * FROM ("+sql+") A  WHERE 1=1 ";
			}else if("YES".equalsIgnoreCase(confStatus)){
				sql ="SELECT * FROM ("+sql+") A WHERE  exists (SELECT 1 FROM T_TRAD_AGENT_INFO_RELA R  where R.Insu_Plan_No=? and R.AGENT_INFO_ID= A.ID ) ";
				String insuPlanNo = StrUtil.getVal(map.get("insuPlanNo"));
				if(StrUtil.isNotEmpty(insuPlanNo)){
					list.add(insuPlanNo);
					islike.add(false);
				}
			}else if("NO".equalsIgnoreCase(confStatus)){
				sql ="SELECT * FROM ("+sql+") A WHERE not exists (SELECT 1 FROM T_TRAD_AGENT_INFO_RELA R  where R.Insu_Plan_No=? and R.AGENT_INFO_ID= A.ID ) ";
				String insuPlanNo = StrUtil.getVal(map.get("insuPlanNo"));
				if(StrUtil.isNotEmpty(insuPlanNo)){
					list.add(insuPlanNo);
					islike.add(false);
				}
			}else{
				sql = "SELECT * FROM ("+sql+") A WHERE 1=1 ";
			}
			
			String userName =StrUtil.getVal(map.get("userName"));
			if(StrUtil.isNotEmpty(userName)){
				sql = sql +" AND A.USERNAME like ? ";
				list.add(userName);
				islike.add(true);
			}
			if(StrUtil.isNotEmpty(tradAgentInfo.getUserCode())){
				sql = sql +" AND A.USERCODE like ? ";
				list.add(tradAgentInfo.getUserCode());
				islike.add(true);
			}
			
			sql = sql +" ORDER BY A.USERCODE ";
			logger.debug("执行SQL：" + sql);


			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt.setObject(i+1, list.get(i));
				}
				
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);
			page.setTotal(total);
			//查出分页
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt1.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt1.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(conn);
		}
		
		return page;
	}

	@Override
	public Page queryPageForTradAgentInfoEdit(Page page, String insuPlanNo) throws Exception {
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			sqlbuf.append("SELECT T.ID AS ID,T.USER_CODE USERCODE,U.USER_NAME AS USERNAME,T.DPT_CDE AS DPTCDE,")
			.append(" T.CHANNLE_CODE CHANNLECODE,D1.CODE_NAME AS CHANNLECODEDES,T.CHANNLE_TYPE AS CHANNLETYPE,D2.CODE_NAME AS CHANNLETYPEDES, ")
			.append(" T.NEW_BSNS_TYPE AS NEWBSNSTYPE,D3.CODE_NAME AS NEWBSNSTYPEDES,T.CATEGORY_CODE AS CATEGORYCODE,D4.CODE_NAME AS CATEGORYCODEDES, ")
			.append(" T.AGENT_NO AS AGENTNO, T.PROTOCOL_NO AS PROTOCOLNO, T.SERVICE_CODE AS SERVICECODE,T.EMP_NO AS EMPNO, ")
			.append(" T.CARD_APPLICANT AS CARDAPPLICANT, T.TEAM_CODE AS TEAMCODE ,R.COMM_RATE as COMMRATE ,R.MARKET_RATE  AS MARKETRATE,R.BUSINESS_RATE  AS BUSRATE ")
			.append(" FROM T_TRAD_AGENT_INFO_RELA R  ")
			.append(" LEFT JOIN T_TRAD_AGENT_INFO T on R.AGENT_INFO_ID = T.ID  ")
			.append(" LEFT JOIN T_FACADE_USER U ON T.USER_CODE = U.USER_CODE  ")
			.append(" LEFT JOIN t_gateway_dict d1 on d1.code_type ='belong_to_channel' and T.CHANNLE_CODE = d1.code ")
			.append(" LEFT JOIN t_gateway_dict d2 on d2.code_type ='channel_type' and T.Channle_Type = d2.code ")
			.append(" LEFT JOIN t_gateway_dict d3 on d3.code_type ='new_business_source' and T.New_Bsns_Type = d3.code ")
			.append(" LEFT JOIN t_gateway_dict d4 on d4.code_type ='old_business_source' and T.Category_Code = d4.code ")
			.append(" WHERE 1=1 ");
			String sql;
			List<String> list=new ArrayList<String>();
			List<Boolean> islike=new ArrayList<Boolean>();
			
			if(StrUtil.isNotEmpty(insuPlanNo)){
				sqlbuf.append(" AND R.INSU_PLAN_NO = ?");
				list.add(insuPlanNo);
				islike.add(false);
			}
			
			sqlbuf.append(" ORDER BY T.USER_CODE  ");
			sql=sqlbuf.toString();
			logger.debug("执行SQL：" + sql);


			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt.setObject(i+1, list.get(i));
				}
				
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);
			page.setTotal(total);
			//查出分页
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt1.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt1.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(conn);
		}
		
		return page;
	}

	@Override
	public Page queryPageForTradAgentInfoType4(Page<TradAgentInfo> page,
			Map<String, Object> map) throws Exception {
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			sqlbuf.append(" SELECT T.ID AS ID, T.Channel_code AS CHANNELCODE,T.SUB_CHANNEL_CODE AS SUBCHANNELCODE,")
			.append(" SC.SUB_CHANNEL_NAME AS SUBCHANNELNAME,SC.ZONE AS ZONE,SC.BRANCH AS BRANCH,SC.BRANCH_NAME AS BRANCHNAME,T.DPT_CDE AS DPTCDE, ")
			.append(" CI.TRANS_TYPE AS TRANSTYPE,U.FK_SUB_CHANNEL_CODE AS FKSUBCHANNELCODE ,U.USER_CODE AS USERCODE , U.USER_NAME AS USERNAME, ")
			.append(" T.CHANNLE_CODE CHANNLECODE,D1.CODE_NAME AS CHANNLECODEDES,T.CHANNLE_TYPE AS CHANNLETYPE,D2.CODE_NAME AS CHANNLETYPEDES, ")
			.append(" T.NEW_BSNS_TYPE AS NEWBSNSTYPE,D3.CODE_NAME AS NEWBSNSTYPEDES,T.CATEGORY_CODE AS CATEGORYCODE,D4.CODE_NAME AS CATEGORYCODEDES, ")
			.append(" T.AGENT_NO AS AGENTNO,T.PROTOCOL_NO AS PROTOCOLNO,T.SERVICE_CODE AS SERVICECODE,")
			.append(" T.EMP_NO AS EMPNO,T.CARD_APPLICANT AS CARDAPPLICANT,T.TEAM_CODE AS TEAMCODE  ")
			.append(" FROM T_TRAD_AGENT_INFO T  ")
			.append(" LEFT JOIN T_GATEWAY_SUB_CHANNEL SC ON T.SUB_CHANNEL_CODE =SC.SUB_CHANNEL_CODE  AND T.CHANNEL_CODE=SC.FK_CHANNEL_CODE ")
			.append(" LEFT JOIN T_FACADE_USER U on  SC.SUB_CHANNEL_CODE=U.FK_SUB_CHANNEL_CODE  AND U.USER_CODE=T.USER_CODE ")
			.append(" LEFT JOIN T_GATEWAY_CHANNEL_INFO CI ON SC.PARENT_CHANNEL_CODE =CI.CHANNEL_CODE ")
			.append(" LEFT JOIN t_gateway_dict d1 on d1.code_type ='belong_to_channel' and T.CHANNLE_CODE = d1.code ")
			.append(" LEFT JOIN t_gateway_dict d2 on d2.code_type ='channel_type' and T.Channle_Type = d2.code ")
			.append(" LEFT JOIN t_gateway_dict d3 on d3.code_type ='new_business_source' and T.New_Bsns_Type = d3.code ")
			.append(" LEFT JOIN t_gateway_dict d4 on d4.code_type ='old_business_source' and T.Category_Code = d4.code ")
			.append(" WHERE 1=1 ");
			String sql;
			List<String> list=new ArrayList<String>();
			List<Boolean> islike=new ArrayList<Boolean>();
			TradAgentInfo tradAgentInfo= page.getType();
			String channelCode =StrUtil.getVal(map.get("channelCode"));
			if(StrUtil.isNotEmpty(channelCode)){
				sqlbuf.append(" AND T.CHANNEL_CODE = ?");
				list.add(channelCode);
				islike.add(false);
			}
			if(StrUtil.isNotEmpty(tradAgentInfo.getAgentType())){
				sqlbuf.append(" AND T.AGENT_TYPE= ?");
				list.add(tradAgentInfo.getAgentType());
				islike.add(false);
			}
			String dept_code =StrUtil.getVal(map.get("dept_code4"));
			if(StrUtil.isNotEmpty(dept_code) ){
				sqlbuf.append(" and t.dpt_cde in (select dept_code from t_core_dept t connect by prior dept_code = snr_dept start with dept_code in ( "+dept_code+" ))");
				/*list.add(dept_code);
				islike.add(false);*/
			}
			sql=sqlbuf.toString();
			
			String confStatus = StrUtil.getVal(map.get("confStatus"));
			if("ALL".equalsIgnoreCase(confStatus)){
				sql = "SELECT * FROM ("+sql+") A  WHERE 1=1 ";
			}else if("YES".equalsIgnoreCase(confStatus)){
				sql ="SELECT * FROM ("+sql+") A WHERE  exists (SELECT 1 FROM T_TRAD_AGENT_INFO_RELA R  where R.Insu_Plan_No=? and R.AGENT_INFO_ID= A.ID )";
				String insuPlanNo = StrUtil.getVal(map.get("insuPlanNo"));
				if(StrUtil.isNotEmpty(insuPlanNo)){
					list.add(insuPlanNo);
					islike.add(false);
				}
			}else if("NO".equalsIgnoreCase(confStatus)){
				sql ="SELECT * FROM ("+sql+") A WHERE not exists (SELECT 1 FROM T_TRAD_AGENT_INFO_RELA R  where R.Insu_Plan_No=? and R.AGENT_INFO_ID= A.ID )";
				String insuPlanNo = StrUtil.getVal(map.get("insuPlanNo"));
				if(StrUtil.isNotEmpty(insuPlanNo)){
					list.add(insuPlanNo);
					islike.add(false);
				}
			}else{
				sql = " SELECT * FROM ("+sql+") A WHERE 1=1 ";
			}
			
			String branch =StrUtil.getVal(map.get("branch"));
			if(StrUtil.isNotEmpty(branch)){
				sql = sql +" AND A.BRANCH like ?";
				list.add(branch);
				islike.add(true);
			}
			String branchName =StrUtil.getVal(map.get("branchName"));
			if(StrUtil.isNotEmpty(branchName)){
				sql = sql +" AND A.BRANCHNAME like ?";
				list.add(branchName);
				islike.add(true);
			}
			
			//sql = sql +" ORDER BY A.USERCODE ";
			logger.debug("执行SQL：" + sql);

			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt.setObject(i+1, list.get(i));
				}
				
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);
			page.setTotal(total);
			//查出分页
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt1.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt1.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(conn);
		}
		
		return page;
	}

	@Override
	public Page queryPageForTradAgentInfoEditType4(Page page, String insuPlanNo)
			throws Exception {
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		Map<String,Object> map=page.getMap();
		String deptStrs=(String) map.get("userdeptstr");
		ResultSet rs = null;
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			sqlbuf.append("SELECT T.ID AS ID, T.Channel_code AS CHANNELCODE,T.SUB_CHANNEL_CODE AS SUBCHANNELCODE, ")
			.append(" SC.SUB_CHANNEL_NAME AS SUBCHANNELNAME,SC.ZONE AS ZONE,SC.BRANCH AS BRANCH,SC.BRANCH_NAME AS BRANCHNAME,T.DPT_CDE AS DPTCDE, ")
			.append(" CI.TRANS_TYPE AS TRANSTYPE,U.USER_CODE AS USERCODE , U.USER_NAME AS USERNAME, ")
			.append(" T.CHANNLE_CODE CHANNLECODE,D1.CODE_NAME AS CHANNLECODEDES,T.CHANNLE_TYPE AS CHANNLETYPE,D2.CODE_NAME AS CHANNLETYPEDES, ")
			.append(" T.NEW_BSNS_TYPE AS NEWBSNSTYPE,D3.CODE_NAME AS NEWBSNSTYPEDES, T.CATEGORY_CODE AS CATEGORYCODE,D4.CODE_NAME AS CATEGORYCODEDES,")
			
			.append(" T.AGENT_NO AS AGENTNO,T.PROTOCOL_NO AS PROTOCOLNO,T.SERVICE_CODE AS SERVICECODE,T.EMP_NO AS EMPNO, ")
			.append(" T.CARD_APPLICANT AS CARDAPPLICANT,T.TEAM_CODE AS TEAMCODE,R.COMM_RATE as COMMRATE, R.MARKET_RATE       AS MARKETRATE, R.BUSINESS_RATE     AS BUSRATE")
			.append(" FROM T_TRAD_AGENT_INFO_RELA R  ")
			.append(" LEFT JOIN T_TRAD_AGENT_INFO T on R.AGENT_INFO_ID = T.ID ")
			.append(" LEFT JOIN T_GATEWAY_SUB_CHANNEL SC ON T.SUB_CHANNEL_CODE =SC.SUB_CHANNEL_CODE  and T.Channel_Code=sc.fk_channel_code")
			.append(" LEFT JOIN T_FACADE_USER U on  SC.SUB_CHANNEL_CODE=U.FK_SUB_CHANNEL_CODE  and U.USER_CODE= T.USER_CODE")
			.append(" LEFT JOIN T_GATEWAY_CHANNEL_INFO CI ON SC.PARENT_CHANNEL_CODE =CI.CHANNEL_CODE ")
			.append(" LEFT JOIN t_gateway_dict d1 on d1.code_type ='belong_to_channel' and T.CHANNLE_CODE = d1.code ")
			.append(" LEFT JOIN t_gateway_dict d2 on d2.code_type ='channel_type' and T.Channle_Type = d2.code ")
			.append(" LEFT JOIN t_gateway_dict d3 on d3.code_type ='new_business_source' and T.New_Bsns_Type = d3.code ")
			.append(" LEFT JOIN t_gateway_dict d4 on d4.code_type ='old_business_source' and T.Category_Code = d4.code ")
			.append(" WHERE 1=1 ");
			
			String sql;
			List<String> list=new ArrayList<String>();
			List<Boolean> islike=new ArrayList<Boolean>();
			
			if(StrUtil.isNotEmpty(insuPlanNo)){
				sqlbuf.append(" AND R.INSU_PLAN_NO = ?");
				list.add(insuPlanNo);
				islike.add(false);
			}
			/**分公司用户控制*/
			if(!"00".equals(deptStrs)){
				sqlbuf.append("  AND T.DPT_CDE IN ( select dept_code from t_core_dept t connect by prior dept_code = snr_dept start with ");
				sqlbuf.append(" dept_code in ("+deptStrs+"))");
			}
			sql=sqlbuf.toString();
			logger.debug("执行SQL：" + sql);


			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt.setObject(i+1, list.get(i));
				}
				
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);
			page.setTotal(total);
			//查出分页
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt1.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt1.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(conn);
		}
		
		return page;
		
	}

	@Override
	public boolean updateTradInsuPlanApplicationNo(TradInsuPlan insuplan)
			throws Exception {
		return this.update("updateApplicationNoByChannelCode", insuplan);
	}


}
